package cc.picc.commons;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.IterableUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author Justin
 * 
 */
public class ExcelHelper {

	private Workbook workbook;

	private InputStream fileStream;

	private int titleRow;

	private int beginRow;

	private int endRow;

	/**
	 * 
	 * @param builder
	 */
	private ExcelHelper(Builder builder) {

		this.fileStream = builder.fileStream;
		this.titleRow = builder.titleRowNumber;
		this.beginRow = builder.beginRowNumber;
		this.endRow = builder.endRowNumber;

		try {
			this.workbook = new XSSFWorkbook(fileStream);
		} catch (IOException e) {
			throw new WorkbookCreationException("Error Occured On Creating Workbook", e);
		}
	}

	/**
	 * 获取最终结果
	 * 
	 * @param <T>
	 * @param rowBeanClass
	 * @param sheetName
	 * @return
	 */
	public <T> List<T> listByTitle(Class<T> rowBeanClass, String sheetName) {

		Sheet sheet = this.workbook.getSheet(sheetName);
		// 如果没有数据（表格里的最后一行都没有达到beginRow）就返回空列表。
		if (sheet.getLastRowNum() < this.beginRow)
			return Collections.emptyList();

		int actualEndRow = (sheet.getLastRowNum() < this.endRow) ? sheet.getLastRowNum() : this.endRow;

		List<Title> titles = titleMapping(sheet);
		List<Field> markedFields = markedFields(rowBeanClass);
		try {
			List<T> list = new ArrayList<>();
			for (int i = this.beginRow; i < actualEndRow; i++) {
				// 如果是标题行或结果为空则跳过当前行
				if (i == this.titleRow || sheet.getRow(i) == null)
					continue;
				T bean = rowBeanClass.newInstance();

				Row row = sheet.getRow(i);
				populateBean(bean, row, titles, markedFields);
				list.add(bean);
			}
			return list;
		} catch (InstantiationException | IllegalAccessException e) {
			throw new ExcelHelperException("Exception Occurred On Initializing rowBeanClass", e);
		}
	}

	/**
	 * 将cell对应的内容set到bean里面去 目前只支持int String double BigDecimal几种类型
	 * 
	 * @param bean
	 * @param cell
	 * @param titles
	 * @return
	 * @throws ExcelHelperException
	 */
	private void populateBean(Object bean, Row row, List<Title> titles, List<Field> markedFields)
			throws IllegalAccessException {
		if (CollectionUtils.isNotEmpty(markedFields)) {
			for (Field field : markedFields) {
				cc.picc.commons.Cell anno = field.getAnnotation(cc.picc.commons.Cell.class);
				String fieldTitle = anno.title();
				int fieldIndex = indexByTitle(fieldTitle, titles);
				if (fieldIndex < 0) // 找不到标题对应的index就继续。不进行转换。
					continue;
				Cell cell = row.getCell(fieldIndex);
				field.setAccessible(true);
				if (field.getType() == String.class)
					field.set(bean, cell.getStringCellValue());
				else if (field.getType() == BigDecimal.class)
					field.set(bean, BigDecimal.valueOf(cell.getNumericCellValue()));
				else if (field.getType() == int.class)
					field.set(bean, BigDecimal.valueOf(cell.getNumericCellValue()).intValue());
				else if (field.getType() == double.class)
					field.set(bean, cell.getNumericCellValue());
				else
					// 其他情况抛出【不支持操作】异常
					throw new ExcelHelperException(String.format(
							"Exception Occurred On Populating Bean: {CellType: %s, %s}", cell.getCellType(), cell));
			}
		} // 为空就不管了
	}

	/**
	 * 根据titleText获取index
	 * 
	 * @param titleText
	 * @param titles
	 * @return
	 */
	private int indexByTitle(final String titleText, List<Title> titles) {
		Title title = IterableUtils.find(titles, (Title object) -> {
			if (StringUtils.equals(titleText, object.getText()))
				return true;
			return false;
		});

		if (title == null)
			return -1;
		return title.getIndex();
	}

	/**
	 * 获取title行的mapping
	 * 
	 * @param sheet
	 * @return
	 */
	private List<Title> titleMapping(Sheet sheet) {
		if (sheet.getLastRowNum() < this.titleRow)
			return Collections.emptyList();

		List<Title> list = new ArrayList<>();
		Row row = sheet.getRow(this.titleRow);

		if (row == null)// 如果没有找到标题行则返回空列表。
			return Collections.emptyList();

		for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
			Cell cell = row.getCell(i);
			if (cell == null) // 存在lastCellNum不等于获取的表格不为null。
				continue;
			Title title = new Title(cell.getStringCellValue(), i);
			list.add(title);
		}
		return list;
	}

	/**
	 * 获取被标记的域，这些域将会被用于对应填充属性。
	 * 
	 * @param <T>
	 * 
	 * @return
	 */
	private <T> List<Field> markedFields(Class<T> rowBeanClass) {
		Field[] fields = rowBeanClass.getDeclaredFields();
		List<Field> list = new ArrayList<>();
		for (Field field : fields) {
			if (field.isAnnotationPresent(cc.picc.commons.Cell.class)) {
				list.add(field);
			}
		}
		return list;
	}

	/**
	 * 
	 * @param is
	 * @param sheetName
	 * @param rowBeanClass
	 * @param <E>
	 * @return
	 */
	public static Builder builder(InputStream is) {
		return new Builder(is);
	}

	/**
	 * 
	 * @author Justin
	 *
	 * @param <E>
	 */
	public static class Builder {
		private InputStream fileStream;
		private int titleRowNumber;
		private int beginRowNumber;
		private int endRowNumber;

		/**
		 * 
		 * @param filePath
		 * @param sheetName
		 * @param rowBeanClass
		 */
		public Builder(InputStream filePath) {
			if (filePath == null)
				throw new IllegalArgumentException();
			this.fileStream = filePath;
			this.titleRowNumber = 0;
			this.beginRowNumber = 1;
			this.endRowNumber = 1;
		}

		/**
		 * 标题行
		 * 
		 * @param rowNumber
		 * @return
		 */
		public Builder title(int rowNumber) {
			this.titleRowNumber = rowNumber;
			return this;
		}

		/**
		 * 从哪一行开始(inclusive)
		 * 
		 * @param rowNumber
		 * @return
		 */
		public Builder begin(int rowNumber) {
			this.beginRowNumber = rowNumber;
			return this;
		}

		/**
		 * 设定最大取到哪一行(Exclusive)
		 * 
		 * @param rowNumber
		 * @return
		 */
		public Builder end(int rowNumber) {
			this.endRowNumber = rowNumber;
			return this;
		}

		/**
		 * 
		 * @return
		 */
		public ExcelHelper build() {
			if (this.beginRowNumber < 0 || (this.beginRowNumber > this.endRowNumber) || this.titleRowNumber < 0)
				throw new IllegalArgumentException();
			return new ExcelHelper(this);
		}
	}

	public Workbook getWorkbook() {
		return workbook;
	}

	public InputStream getFileStream() {
		return fileStream;
	}
}

/**
 * title文字和index的对照表
 * 
 * @author Justin
 *
 */
class Title {
	private String titleText;
	private int titleIndex;

	public Title(String titleText, int titleIndex) {
		super();
		this.titleText = titleText;
		this.titleIndex = titleIndex;
	}

	public String getText() {
		return titleText;
	}

	public int getIndex() {
		return titleIndex;
	}

	public void setText(String text) {
		this.titleText = text;
	}

	public void setIndex(int index) {
		this.titleIndex = index;
	}

}
